﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using SD.IDAL;
using SD.Entities;
using System.Data;
using System.Data.SqlClient;

namespace SD.DAL
{
    public class CountyDAO : BaseDAO, ICountyDAO
    {
        #region ICountyDAO Members

        /// <summary>
        /// get all county
        /// </summary>
        /// <returns></returns>
        public List<County> GetAll()
        {
            // Obtain SqlCommand object
            SqlCommand comm = CreateCommand();

            // Assign stored procedure
            comm.CommandText = "CountyGetAll";

            // Execute stored procedur
            DataTable dt = ExecuteSelectCommand(comm);

            // Create list county objects
            List<County> list = new List<County>();

            // Add each county in table to list
            foreach (DataRow row in dt.Rows)
            {
                County c = new County();
                c.CountyID = row["CountyID"].ToString();
                c.Name = row["Name"].ToString();
                list.Add(c);
            }
            
            // return list county
            return list;
        }

        /// <summary>
        /// get county by id
        /// </summary>
        /// <param name="countyId"></param>
        /// <returns></returns>
        public County GetById(string countyId)
        {
            // Obtain SqlCommand object
            SqlCommand comm = CreateCommand();

            // Assign stored procedure
            comm.CommandText = "CountyGetById";

            // Add parameters to stored procedure
            comm.Parameters.AddWithValue("@CountyID", countyId);

            // Execute stored procedur
            DataTable dt = ExecuteSelectCommand(comm);

            County c = null;

            // if table has data
            if (dt.Rows.Count > 0)
            {
                c = new County();
                c.CountyID = dt.Rows[0]["CountyID"].ToString();
                c.Name = dt.Rows[0]["Name"].ToString();
            }

            return c;
        }

        /// <summary>
        /// Insert new county
        /// </summary>
        /// <param name="county"></param>
        /// <returns></returns>
        public bool Insert(County county)
        {
            // Obtain SqlCommand object
            SqlCommand comm = CreateCommand();

            // Assign stored procedure
            comm.CommandText = "CountyInsert";

            // Add parameters to stored procedure
            comm.Parameters.AddWithValue("@Name", county.Name);

            int result = -1;
            try
            {
                // Execute stored procedur
                result = ExecuteNonQuery(comm);
            }
            catch (Exception ex)
            {
                throw ex;
            }

            // return true if insert successfully, otherwise return false
            return (result != -1);
        }

        /// <summary>
        /// Update county
        /// </summary>
        /// <param name="county"></param>
        /// <returns></returns>
        public bool Update(County county)
        {
            return false;
        }

        /// <summary>
        /// Delete county
        /// </summary>
        /// <param name="countyId"></param>
        /// <returns></returns>
        public bool Delete(string countyId)
        {
            return false;
        }

        #endregion

        #region ICountyDAO Members

        /// <summary>
        /// Get county by name
        /// </summary>
        /// <param name="name"></param>
        /// <returns></returns>
        public County GetByName(string name)
        {
            // Obtain SqlCommand object
            SqlCommand comm = CreateCommand();

            // Assign stored procedure
            comm.CommandText = "CountyGetByName";

            // Add paramteres to stored procedure
            comm.Parameters.AddWithValue("@Name", name);

            // Execute stored procedur
            DataTable dt = ExecuteSelectCommand(comm);

            County c = null;

            // If table has data
            if (dt.Rows.Count > 0)
            {
                DataRow row = dt.Rows[0];
                c.CountyID = row["CountyID"].ToString();
                c.Name = row["Name"].ToString();
            }

            // return county
            return c;
        }

        #endregion
    }
}
